/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */
package dbLayer;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Set;

import modelLayer.Booking;
import modelLayer.ChartecCode;
import modelLayer.Disposition;
import modelLayer.Driver;
import modelLayer.Trailer;

public class DbDisposition {
	private Connection con;
	private DbDriver dbDriver;
	private DbTrailer dbTrailer;
	private DbChartecCode dbChartecCode;
	private DbBooking dbBooking;

	/**
	 * Constructor
	 */
	public DbDisposition() {
		con = DbConnection.getInstance().getDBcon();
		dbDriver = new DbDriver();
		dbTrailer = new DbTrailer();
		dbChartecCode = new DbChartecCode();
		dbBooking = new DbBooking();
	}


	/**
	 * Method for retrieving an disposition object from the database.
	 * 
	 * @param dispId				The id of the disposition.
	 * @param retrieveAssociation	True if associations are wanted, false if not.
	 * @return						The disposition object.
	 */
	public Disposition retrieveById(int dispId, boolean retrieveAssociation) {
		String wClause = "dispId = " + dispId;
		return singleWhere(wClause, retrieveAssociation);
	}

	/**
	 * Method for retrieving dispositions belonging to a specific dispositionList.
	 * 
	 * @param dispListId			The id of the dispositionList.
	 * @param retrieveAssociation	True if associations are wanted, false if not.
	 * @return						An ArrayList of dispositions.
	 */
	public ArrayList<Disposition> retrieveByDispList(int dispListId, boolean retrieveAssociation) {
		String wClause = "dispListId = " + dispListId;
		return multiWhere(wClause, retrieveAssociation);
	}

	/**
	 * Method for inserting data into disposition table in database.
	 * 
	 * @param dispListId	The disposition list id.
	 * @param disp 			The disposition object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int insertIntoDisposition(Disposition disp, int dispListId) throws Exception {
		int rc = -1; //Rows affected in database
		PreparedStatement prpStmt = null;
		String query = "INSERT INTO disposition(dispId, chartecNo, dispListId, driverId, trailerId, chartecCode, deliveryNo) " +
				"VALUES (?,?,?,?,?,?,?)";
		try {
			prpStmt = con.prepareStatement(query);
			prpStmt.setInt(1, disp.getDispId());
			prpStmt.setInt(2, disp.getChartecNo());
			prpStmt.setInt(3, dispListId);
			prpStmt.setInt(4, disp.getDriver().getDriverId());
			prpStmt.setInt(5, disp.getTrailer().getTrailerId());
			prpStmt.setInt(6, disp.getChartecCode().getChartecCode());
			prpStmt.setString(7, disp.getDeliveryNo());
			prpStmt.setQueryTimeout(5);
			rc = prpStmt.executeUpdate();
			prpStmt.close();
		} catch(Exception e) {
			throw new Exception("Insert into disposition exception: " + e.getMessage());
		}
		return rc;
	}

	/**
	 * Method for inserting data into bookingSequence table in database.
	 * 
	 * @param dispId		The id of the disposition.
	 * @param sequenceNo	The sequence no the booking is placed on.
	 * @param bookingId		The id of the booking.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int insertIntoBookingSequence(int dispId, int sequenceNo, int bookingId) throws Exception {
		int rc = -1; //Rows affected in database
		PreparedStatement prpStmt = null;
		//System.out.println("SequenceNo: " + sequenceNo);
		String query = "INSERT INTO bookingSequence(dispId, sequenceNo, bookingId) " +
				"VALUES (?,?,?)";
		try {
			prpStmt = con.prepareStatement(query);
			prpStmt.setInt(1, dispId);
			prpStmt.setInt(2, sequenceNo);
			prpStmt.setInt(3, bookingId);
			prpStmt.setQueryTimeout(5);
			rc = prpStmt.executeUpdate();
			prpStmt.close();
		} catch(Exception e) {
			throw new Exception("Insert into bookingSequence exception: " + e.getMessage());
		}
		return rc;
	}

	/**
	 * Method for updating data about a disposition in the database.
	 * 
	 * @param dispListId	The disposition list id.
	 * @param disp 			The disposition object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int update(Disposition disp,  int dispListId) throws Exception {
		int rc = -1; //Rows affected in database

		String query = "UPDATE disposition SET " +
				"chartecNo = " + disp.getChartecNo() +
				", dispListId = " + dispListId +
				", driverId = " + disp.getDriver().getDriverId() +
				", trailerId = " + disp.getTrailer().getTrailerId() +
				", chartecCode = " + disp.getChartecCode().getChartecCode() +
				", deliveryNo = '" + disp.getDeliveryNo() +
				"' WHERE dispId = " + disp.getDispId();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {
			throw new Exception("Update exception in disposition db: " + e);
		}
		rc += updateBookingSequence(disp);
		return rc;
	}

	/**
	 * Method for updating all bookings associated to one disposition in the database.
	 * 
	 * @param disp 			The disposition object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int updateBookingSequence(Disposition disp) throws Exception {
		int rc = 1; //Rows affected in database
		Set<Integer> keyList = disp.getBookings().keySet();
		if (keyList.size() < bookingSequenceTableCount(disp.getDispId())) {
			for (int key = keyList.size() + 1; key <= bookingSequenceTableCount(disp.getDispId()); key ++) {
				rc += deleteFromBookingSequence(disp.getDispId(), key);
			}
		}
		
		for (int key : keyList) {
			int sequenceNo = key;
			int bookingId = disp.getBookings().get(key).getBookingId();
			String query = "UPDATE bookingSequence SET bookingId = " + bookingId +
					" WHERE dispId = " + disp.getDispId() +
					" AND sequenceNo = " + sequenceNo;
			System.out.println(query);
			try {
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc += stmt.executeUpdate(query);
				stmt.close();
			} catch (Exception e) {
				throw new Exception("Update exception in disposition db: " + e);
			}
		}
		return rc;
	}

	/**
	 * Method for deleting a disposition from the database.
	 * 
	 * @param disp 			The disposition object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int deleteFromDisposition(Disposition disp) throws Exception {
		int rc = -1; //Rows affected in database

		String query = "DELETE FROM disposition WHERE dispId = " + disp.getDispId();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			throw new Exception("Delete exception in disposition db: " + e);
		}
		return rc;
	}

	/**
	 * Method used when bookingSequence is updated.
	 *  
	 * @param dispId		The id of the disposition.
	 * @param sequenceNo	The sequence no of the booking.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	private int deleteFromBookingSequence(int dispId, int sequenceNo) throws Exception {
		int rc = -1; //Rows affected in database

		String query = "DELETE FROM bookingSequence WHERE dispId = " + dispId +
				" AND sequenceNo = " + sequenceNo;
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			throw new Exception("Delete from bookingSequence exception: " + e.getMessage());
		}		
		return rc;
	}

	/**
	 * Method that returns a list of disposition objects.
	 * MultiWhere is used when we select more than one disposition from the database.
	 * 
	 * @param wClause				The where-clause in the select query.
	 * @param retrieveAssociation	True if associations are wanted, false if not.
	 * @return						ArrayList containing Disposition objects.
	 */
	private ArrayList<Disposition> multiWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Disposition> list = new ArrayList<Disposition>();
		String query = buildSelectQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Disposition dispObj = buildDisposition(results);
				list.add(dispObj);
			}
			stmt.close();

			if (retrieveAssociation) {
				for(Disposition dispObj : list) {
					retrieveAssociation(dispObj);
				}
			}
		} catch(Exception e) {}
		return list;
	}

	/**
	 * Method that returns an disposition object.
	 * SingleWhere is used when we only select one disposition from the database.
	 * 
	 * @param wClause				The where-clause in the select query.
	 * @param retrieveAssociation	True if associations are wanted, false if not.
	 * @return						The disposition object.
	 */
	private Disposition singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Disposition dispObj = new Disposition();
		String query = buildSelectQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				dispObj = buildDisposition(results);
				stmt.close();

				if (retrieveAssociation) {
					retrieveAssociation(dispObj);
				}
			} else {
				dispObj = null;
			}
		} catch(Exception e) {}
		return dispObj;
	}

	/**
	 * Method that builds the SQL select query.
	 * 
	 * @param wClause	The where-clause in the query.
	 * @return 			The query string.
	 */
	private String buildSelectQuery(String wClause) {
		String query = "SELECT dispId, chartecNo, dispListId, driverId, trailerId, chartecCode, deliveryNo FROM Disposition";

		if (wClause.length() > 0) {
			query += " WHERE " + wClause;
		}
		return query;
	}

	/**
	 * Method that builds the disposition object from data retrieved in the datebase.
	 * 
	 * @param results	The resultSet returned from the database.
	 * @return			The disposition object.
	 */
	private Disposition buildDisposition(ResultSet results) {
		Disposition dispObj = new Disposition();

		try {
			dispObj.setDispId(results.getInt("dispId"));
			dispObj.setChartecNo(results.getInt("chartecNo"));
			dispObj.setDriver(new Driver(results.getInt("driverId")));
			dispObj.setTrailer(new Trailer(results.getInt("trailerId")));
			dispObj.setChartecCode(new ChartecCode(results.getInt("chartecCode")));
			dispObj.setDeliveryNo(results.getString("deliveryNo"));
			dispObj.setBookings(new HashMap<Integer, Booking>());			
		} catch (Exception e) {}
		return dispObj;
	}

	/**
	 * Method that adds all associations to an disposition object.
	 *  
	 * @param dispObj		The disposition object.
	 */
	private void retrieveAssociation(Disposition dispObj) {
		int driverId = dispObj.getDriver().getDriverId();
		Driver driver = dbDriver.retrieveById(driverId);
		dispObj.setDriver(driver);

		int trailerId = dispObj.getTrailer().getTrailerId();
		Trailer trailer = dbTrailer.retrieveById(trailerId);
		dispObj.setTrailer(trailer);

		int chartecCode = dispObj.getChartecCode().getChartecCode();
		ChartecCode cCode = dbChartecCode.retrieveByCode(chartecCode);
		dispObj.setChartecCode(cCode);

		HashMap<Integer,Booking> bookings = retrieveBookingListByDispId(dispObj.getDispId());
		dispObj.setBookings(bookings);

	}

	/**
	 * Method that builds and returns a HashMap containing Bookings.
	 * 
	 * @param dispId		The id of the disposition.
	 * @return				The HashMap.
	 */
	private HashMap<Integer, Booking> retrieveBookingListByDispId(int dispId) {
		HashMap<Integer,Booking> list = new HashMap<Integer,Booking>();
		String query = "SELECT sequenceNo, bookingId FROM BookingSequence " +
				" WHERE dispId = " + dispId;
		ResultSet results;
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while(results.next()) {
				int key = results.getInt("sequenceNo");
				Booking b = dbBooking.retrieveById(results.getInt("bookingId"));
				list.put(key, b);
			}
			stmt.close();
		} catch(Exception e) {}
		return list;
	}

	/**
	 * Method that counts the number of bookings for one dispId in the database.
	 * 
	 * @param dispId
	 * @return			The number of bookings in the database for the dispId.
	 */
	private int bookingSequenceTableCount(int dispId) {
		int tableCount = 0;
		ResultSet results; 
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery("select COUNT(*) from bookingSequence where dispId = " + dispId);

			if (results.next() ) {
				tableCount = results.getInt(1);
				stmt.close();
			} else {
				tableCount = 0;
			}
		} catch (Exception e) {}
		return tableCount;
	}
	
}